package DBOperation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBOperation 
{
	String username = "root";
		
	String password = "root";
		
	String url = "jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8";
		
	Connection con = null;
	
	public ResultSet dbQuery(String sql)
	{		
		ResultSet rs = null;

		try 
		{
			Statement statement = con.createStatement();
			rs = statement.executeQuery(sql);
			return rs;
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return rs;
		}
	}
	
	public int dbUpdate(String sql)
	{		
		int result = 0;

		try 
		{
			Statement statement = con.createStatement();
			result = statement.executeUpdate(sql);
			return result;
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			return result;
		}
	}
	
	/**
	 * 打开数据库连接
	 * 当con为空时，创建连接；否则直接返回
	 * @return con
	 */
	public Connection openConnection()
	{
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			
			if(con == null)
				con = DriverManager.getConnection(url, username, password);
			
			return con;
		} 
		catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			return con;
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return con;
		}
	}
	
	/**
	 * 关闭数据库连接
	 * 如果con不为空，关闭连接
	 * @param con
	 */
	public void closeConnection(Connection con)
	{
		try 
		{
			if(con != null)
				con.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		
	/**
	 * 验证当前用户是否为合法用户
	 * @param username
	 * @param password
	 * @return
	 */
	public ResultSet userLogin(String username, String password)
	{
		String sql = "select * from user where name = '" + username + "' and password = '" + password + "'";
		
		//System.out.println(sql);
		con = openConnection();
		ResultSet rs = dbQuery(sql);
		return rs;
	}
	
	/**
	 * 查询所有Feed
	 * @return
	 */
	public ResultSet getAllFeeds()
	{
		String sql = "select f.id as fid, f. userid, f.content, f.gmt_modified, " +
				"u.name as username, u.company from feed f, user u " +
				"where f.userid=u.id " +
				"order by f.gmt_modified desc;";
		con = openConnection();
		ResultSet rs = dbQuery(sql);
		return rs;
	}
	
	/**
	 * 查询当前用户发的Feed
	 * @return
	 */
	public ResultSet getMyFeeds(int userid)
	{
		String sql = "select f.id as fid, f.userid, f.content, f.gmt_modified, " +
				"u.name as username, u.company from feed f, user u " +
				"where f.userid=u.id and u.id=" + userid +
				" order by f.gmt_modified desc;";
		con = openConnection();
		ResultSet rs = dbQuery(sql);
		return rs;
	}
	
	public int publishFeed(int userid, String content)
	{
		String sql = "insert into feed(userid, content, gmt_create, gmt_modified) " +
				"values(" + userid + ",\"" + content + "\", now(), now());";
		con = openConnection();
		int result = dbUpdate(sql);
		return result;
	}
	
	public ResultSet getAllComments(int fid)
	{
		System.out.println("--db--"+fid);
		String sql = "select u.name, u.company, c.content, c.gmt_create " +
				"from user u, feed_comment fc, comment c " +
				"where u.id=c.userid and c.id=fc.comment_id and fc.feed_id=" + fid +";";
		con = openConnection();
		ResultSet rs = dbQuery(sql);
		return rs;
	}
	
	public int publishComment(int userid, int fid, String content)
	{
		String maxCommentIdSql = "select max(id) from comment;";
		con = openConnection();
		ResultSet rs = dbQuery(maxCommentIdSql);
		int maxCommentId = 0;
		try {
			while(rs.next())
			{
				maxCommentId = rs.getInt("max(id)") + 1;
				System.out.println("--maxCommentId--"+maxCommentId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		String commentSql = "insert into comment(id, userid, content, gmt_create)" +
				"values(" + maxCommentId + "," + userid + ",\"" + content + "\", now());";
		int commentResult = dbUpdate(commentSql);
		if(commentResult == 1)
		{
			String feed_commentSql = "insert into feed_comment(feed_id, comment_id)" +
					"values(" + fid + "," + maxCommentId + ");";
			int feed_commentResult = dbUpdate(feed_commentSql);
			return feed_commentResult;
			
		}
		return 0;
	}
	
	public String getJson(String[] arrKey, String[] arrValue)
	{
		String result = "(";
		for(int i=0; i<arrValue.length; i++)
		{
			result += arrKey[i] + ":" + arrValue[i] + ",";
		}
		result = result.substring(0, result.length()-1);
		result +=")";
		//System.out.println("--getJson--"+result);
		return result;
	}
	
}
